/****** Object:  StoredProcedure [dbo].[AuthZ_spGetParentRoles]    Script Date: 06/24/2009 23:42:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetParentRoles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetParentRoles]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetParentRoles]    Script Date: 06/24/2009 23:42:50 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-17-2009>
-- Description:	<Retrieves all Roles for the given role.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetParentRoles]

	@role as nvarchar(50)

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		DECLARE @parentRoles As TABLE (RoleID int, ParentRoleID int, RoleName nvarchar(50))

		INSERT INTO @parentRoles

		/*Get the immediate parents of the passed in role. */
		SELECT parent.RoleID
		, parent.ParentRoleID
		, parent.RoleName
		FROM [dbo].[Role] as parent
		JOIN [dbo].[Role] as child
		ON   child.ParentRoleID = parent.RoleID
		AND  child.RoleName = @role
		
		WHILE @@ROWCOUNT > 0
		BEGIN

			INSERT INTO @parentRoles

			/*Get the children's children. */
			SELECT parent.RoleID
			, parent.ParentRoleID
			, parent.RoleName
			FROM @parentRoles as child
			JOIN [dbo].[Role] as parent
			ON   child.ParentRoleID = parent.RoleID  --Gets the parent
			AND  not exists (           --Don't return rows that already exist.
			SELECT child2.RoleID 
			FROM @parentRoles as child2
			WHERE child2.RoleID = parent.RoleID)
		END

		SELECT 
		parent.RoleName
		FROM @parentRoles as parent

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END


GO


